********************************************************************************
* This the main file to generate regression tables on
* 1. cash holding and acquisition
* 2. cash holding and liquidity cost
* This file calls acquisition_cash.do, cash_demand_HP_filter.do and cash_demand_BK_filter.do
********************************************************************************


clear
*cd "C:\Users\zhuy\Dropbox\compustat"  //enter working directory. You should change this line to the folder in your computer
cd "/Users/yuzhu/Desktop/codes_and_data_final_submisson/micro_level_evidence"

use "compustat2021.dta"
set matsize 11000
*===============================================================
* drop observations whose 
*===============================================================
sort gvkey fyear
drop if capx==. | aqc==. | sppe ==.
drop if fyear < 1971
drop if fyear>2018
drop if loc == "CAN"
by gvkey: gen id = 1 if fyear[_n]==fyear[_n-1]
replace id=0 if id==.
by gvkey: gen repid = sum(id)
drop if repid>0
* exclude financial firms
drop if sic>6000 & sic<6500
drop if sic<1000 | sic>9000 
*drop if sic >=4900 & sic<=4999

merge m:1 fyear using "liquidity_costs.dta" // merge with cpi data
drop _merge

drop if fyear < 1971

******************************************
*generate higher level industry code
******************************************

/*gen bigsic = 1 if sic<1000
replace bigsic=2 if sic>=1000&sic<1500
replace bigsic=3 if sic>=1500&sic<1800
replace bigsic=4 if sic>=2000&sic<4000
replace bigsic=5 if sic>=4000&sic<5000
replace bigsic=6 if sic>=5000&sic<5200
replace bigsic=7 if sic>=5200&sic<6000
replace bigsic=8 if sic>=6000&sic<6800
replace bigsic=9 if sic>=7000&sic<9000
replace bigsic=10 if sic>=9000&sic<9730
replace bigsic=11 if sic>=9900&sic<10000*/
replace cpi = cpi/100
gen bigsic = floor(sic/100)
*by gvkey: egen totalat = mean(at)
*by gvkey: gen idquantile = 1 if _n==1
*xtile ptile = totalat if idquantile==1,nq(100)
*by gvkey: replace ptile = ptile[1]
*gen fconstr=1 if ptile<=30
*replace fconstr=0 if ptile>=70
*replace fconstr=2 if ptile<70&ptile>30
merge m:1 state fyear using statecpi_annual
replace annual_pi = annual_pi/100
replace annual_pi_t = annual_pi_t/100
replace annual_pi_nt = annual_pi_nt/100
*drop if _merge ==1

*************************************************
* Normalize variables by cpi
*************************************************
gen at_r = at/cpi    // _r means normalized to 1971 prices
gen che_r = log(che/cpi)
gen ch_r = ch/cpi
gen aqc_r = log(aqc/cpi)
gen sale_r = log(sale/cpi)
gen logat_r = log(at_r)
gen capx_r = log(capx/cpi)
gen capxv_r = capxv/cpi
gen sppe_r = sppe/cpi
gen ebit_r = log(ebit/cpi)
*******************************************************
* normalize variable by total asset
*******************************************************
gen cheat = log(che/at)
gen capxat = log(capx/at)
gen lratio = (dltt+dlc)/seq
gen lratio_l = dltt/seq
gen lratio_c = dlc/seq
gen lratio_a = (at - seq)/seq /* alternative calculation of leverage ratio: total liability/shareholders equity*/
gen capxvat = capxv/at  //capital expenditure on sppe
gen logat =log(at)
gen chat = ch/at
gen aqcat = log(aqc/at)
gen sppeat = sppe/at
gen saleat=sale/at
gen ebitat= log(ebit/at)
bysort bigsic fyear: egen avg_at = mean(at) 
gen relative_size = log(at/avg_at)

*replace lratio = lratio/10

gen chratio = ch/che // ratio of cash to cash and cash equivalent: higher means more liquid
gen aqccapx = aqc/(capx+aqc) //fraction of acquisition 
gen logaqccapx = log(aqccapx)
gen aqcdummy = 1 if aqc>0
replace aqcdummy = 0 if aqc<=0

* generate time dummies
gen five_years = floor(fyear/2) 
tabulate five_years, generate(timedummy)
tabulate fyear, generate(yeardummy)

***********************************************************************
* startup: id for first three years to approximate start-ups
* exceed_id: 1 if the aqc exceeds assets for some period
***********************************************************************
xtset gvkey fyear
gen pre_start_id = l3.cheat 
gen new=1 if pre_start_id ==.
by gvkey: egen max_aqcat = max(aqcat)
gen exceed_id = 1 if max_aqcat>0
drop pre_start_id 


xtset gvkey fyear

bysort gvkey:gen lcheat = l.cheat

xtset gvkey fyear


* Annual time dummies
estimates clear 

qui:xtreg cheat annual_pi logat_r ebitat  lratio_c capxat i.fyear if at>=0, fe r
estimates store m1

qui:xtreg cheat annual_pi logat_r ebitat  lratio_c capxat fyear#bigsic if at>=0, fe r
estimates store m2

esttab m1 m2 using "tables/md_at_micro_log_final.tex", ///
 keep(annual_pi logat_r ebitat lratio_c capxat ) cells(b(star fmt(3)) se(par fmt(3))) ///
 starlevels(* 0.10 ** 0.05 *** 0.01) stats(N r2_a r2_w) varlabels(ebitat "Sales" logat_r "Log Asset" lratio_c "Leverage" capxat "Capital Exp." inflation "Inflation" inflation_c "Inflation (Cycle)") ///
 nonumbers mtitles("Fixed Effects" "Fixed Effects") addnotes("Robust standard errors are in brackets and are clustered at firm level.") legend replace

esttab m1 m2 using  "tables/md_at_micro_log_final.csv", ///
 keep(annual_pi logat_r ebitat lratio_c capxat ) cells(b(star fmt(3)) se(par fmt(3))) ///
 starlevels(* 0.10 ** 0.05 *** 0.01) stats(N r2_a r2_w) varlabels(ebitat "Sales" logat_r "Log Asset" lratio_c "Leverage" capxat "Capital Exp." inflation "Inflation" inflation_c "Inflation (Cycle)") ///
 nonumbers mtitles("Fixed Effects" "Fixed Effects") addnotes("Robust standard errors are in brackets and are clustered at firm level.") legend replace

